﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web.Security;

namespace FB.Membership.Providers
{
    public class SqlPermissionProvider : AspNetPermissionProvider
    {
        string _ConnString = string.Empty;
        

        public string connectionStringName { get; set; }


        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
        {
            base.Initialize(name, config);

            #region initialize custom attributes

            this.connectionStringName = config["connectionStringName"];

            #endregion

            if (string.IsNullOrEmpty(this.connectionStringName))
                throw new ConfigurationErrorsException("connectionStringName must be set to the appropriate value");
            
            if (ConfigurationManager.ConnectionStrings[this.connectionStringName] == null)
                throw new ConfigurationErrorsException("connection string Name not find.");

            _ConnString = ConfigurationManager.ConnectionStrings[this.connectionStringName].ConnectionString;
        }


        public override bool Validate<P>(List<Permission> permList, P permFlag) 
        {

            var SecFlag = GetSecurityFlag(permFlag);
            var p = permList.Find(w => w.Module == SecFlag.Module && w.Flag == SecFlag.Flag);
            if (p != null)
            {
                int iPerm = Convert.ToInt32(permFlag);
                return (p.Privilege & iPerm) == iPerm;
            }

            return false;
        }

        public override bool ValidateModule<M>(List<Permission> permList, M module)
        {
          
            int moduleId = Convert.ToInt32(module);
            return permList.Exists(t => t.Module == moduleId);
        }
        public override bool AnyOf<P>(List<Permission> permList)
        {
            var type = typeof(P);
            var SecFlag = GetSecurityFlag(type);

            return permList.Any(k => k.Module == SecFlag.Module && k.Flag == SecFlag.Flag);
        }


        public override List<Permission> GetUserPermissions(MembershipUser user)
        {
            return GetUserPermissions(user, true);
            
        }

        public override List<Permission> GetUserPermissions(MembershipUser user, bool includeRole = true)
        {
            var result = new List<Permission>();
            if (null != user && !string.IsNullOrEmpty(user.UserName))
            {
                using (var SqlConn = new SqlConnection(this._ConnString))
                {
                    var sql = @"SELECT DISTINCT ModuleId, MenuId, SUM(FuncId) AS 'FuncId' FROM [dbo].[UsersInPermissions] WHERE MenuId >= 100 AND UserId = @UserID GROUP BY ModuleId, MenuId ORDER BY ModuleId, MenuId";

                    if (includeRole)
                    {
                        sql = @"SELECT ModuleId, MenuId, SUM(FuncId) AS 'FuncId' FROM (
                                SELECT DISTINCT ModuleId, MenuId, FuncId FROM (
                                    SELECT ModuleId, MenuId, FuncId FROM [dbo].[PermissionsInRoles] WHERE MenuId >= 100 AND roleId IN 
                                        (SELECT UR.RoleId FROM [dbo].[aspnet_UsersInRoles] UR, [dbo].[aspnet_Users] U WHERE UR.UserId = U.UserId  AND U.LoweredUserName =  @UserName)
	                                UNION
	                                SELECT ModuleId, MenuId, FuncId FROM [dbo].[UsersInPermissions] WHERE MenuId >= 100 AND UserId = @UserID
                                ) AS u
                            ) AS t GROUP BY ModuleId, MenuId 
                            ORDER BY ModuleId, MenuId";
                    }

                    using (var command = new SqlCommand(sql, SqlConn))
                    {
                        command.Parameters.Add(new SqlParameter("@UserName", user.UserName.ToLower()));
                        command.Parameters.Add(new SqlParameter("@UserID", user.ProviderUserKey));

                        command.Connection.Open();
                        using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (reader.Read())
                            {
                                result.Add(new Permission
                                {
                                    Module = (int)reader["ModuleId"],
                                    Flag = (int)reader["MenuId"],
                                    Privilege = Convert.ToInt64(reader["FuncId"])
                                });
                            }
                        }
                    }
                }
            }

            return result;
        }


        public override List<Permission> GetRolesPermissions(params string[] rolesName)
        {
            var result = new List<Permission>();

            if (rolesName != null && rolesName.Length > 0)
            {
                using (var SqlConn = new SqlConnection(this._ConnString))
                {
                    var counter = 0;
                    var ParamList = rolesName.ToList().ConvertAll(r =>
                    {
                        counter++;
                        return string.Format("@R{0}", counter - 1);
                    });

                    var sql = string.Format(@"SELECT ModuleId, MenuId, SUM(FuncId) AS 'FuncId' FROM (
                                                SELECT DISTINCT ModuleId, MenuId, FuncId FROM dbo.PermissionsInRoles WHERE MenuId >= 100 AND 
                                                roleId IN (SELECT RoleId FROM dbo.aspnet_Roles R WHERE R.LoweredRoleName IN ({0}))
                                            ) AS d 
                                            GROUP BY ModuleId, MenuId 
                                            ORDER BY ModuleId, MenuId", string.Join(",", ParamList));

                    using (var command = new SqlCommand(sql, SqlConn))
                    {
                        //reset counter
                        counter = 0;
                        foreach (var s in rolesName)
                        {
                            command.Parameters.Add(new SqlParameter(string.Format("@R{0}", counter), s.ToLower()));
                            counter++;
                        }

                        command.Connection.Open();
                        using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (reader.Read())
                            {
                                result.Add(new Permission { Module = (int)reader["ModuleId"], Flag = (int)reader["MenuId"], Privilege = Convert.ToInt64(reader["FuncId"]) });
                            }
                        }
                    }
                }
            }

            return result;
        }

        public override List<Permission> GetRolePermissions(string roleName)
        {
            var result = new List<Permission>();
            using (var SqlConn = new SqlConnection(this._ConnString))
            {
                var sql = @"SELECT ModuleId, MenuId, SUM(FuncId) AS 'FuncId' FROM dbo.PermissionsInRoles WHERE MenuId >= 100 AND 
                            roleId = (SELECT  RoleId FROM dbo.aspnet_Roles R WHERE R.LoweredRoleName = @RoleName)
                            GROUP BY ModuleId, MenuId ORDER BY ModuleId, MenuId";

                using (var command = new SqlCommand(sql, SqlConn))
                {
                    command.Parameters.Add(new SqlParameter("@RoleName", roleName.ToLower()));
                    command.Connection.Open();
                    using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            result.Add(new Permission { Module = (int)reader["ModuleId"], Flag = (int)reader["MenuId"], Privilege = Convert.ToInt64(reader["FuncId"]) });
                        }
                    }
                }
            }

            return result;
        }


        public override List<string> FindUserHasPermission<P>(P permFlag)
        {
            var result = new List<string>();
            var SecFlag = GetSecurityFlag(permFlag);

            using (var SqlConn = new SqlConnection(this._ConnString))
            {
                var sql = @"SELECT DISTINCT * FROM (
                                SELECT U.UserName FROM dbo.aspnet_UsersInRoles UR
                                LEFT JOIN dbo.aspnet_Users U ON U.UserId = UR.UserId
                                WHERE UR.RoleId IN (SELECT PR.RoleId FROM dbo.PermissionsInRoles PR WHERE PR.ModuleId=@ModuleId AND PR.MenuId=@MenuId AND PR.FuncId=@FuncId)
                                UNION
                                SELECT UP.UserName FROM UsersInPermissions UP WHERE UP.ModuleId=@ModuleId AND UP.MenuId=@MenuId AND UP.FuncId=@FuncId 
                           ) AS t1";

                var command = new SqlCommand(sql, SqlConn);
                command.Parameters.Add(new SqlParameter("@ModuleId", SecFlag.Module));
                command.Parameters.Add(new SqlParameter("@MenuId", SecFlag.Flag));
                command.Parameters.Add(new SqlParameter("@FuncId", Convert.ToInt32(permFlag)));


                command.Connection.Open();
                using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        result.Add(reader["UserName"].ToString());
                    }
                }

            }

            return result;
        }


        public override void AddToUser<P>(MembershipUser user, P permFlag)
        //public override void AddToUser<P>(string userName, P permFlag)
        {
            var SecFlag = GetSecurityFlag(permFlag);
            using (var SqlConn = new SqlConnection(this._ConnString))
            {
                var sql = @"INSERT INTO dbo.UsersInPermissions (UserName, ModuleId, MenuId, FuncId, FlagName, UserId)
                            SELECT @UserName, @ModuleId, @MenuId, @FuncId, @FlagName, @UserId
                            WHERE (NOT EXISTS (SELECT * FROM UsersInPermissions WHERE (UserId = @UserId) AND (ModuleId = @ModuleId) AND (MenuId = @MenuId) AND (FuncId = @FuncId)))";

                var command = new SqlCommand(sql, SqlConn);
                command.Parameters.Add(new SqlParameter("@UserName", user.UserName.ToLower()));
                command.Parameters.Add(new SqlParameter("@ModuleId", SecFlag.Module));
                command.Parameters.Add(new SqlParameter("@MenuId", SecFlag.Flag));
                command.Parameters.Add(new SqlParameter("@FuncId", Convert.ToInt32(permFlag)));
                command.Parameters.Add(new SqlParameter("@FlagName", getFlagName(permFlag)));
                command.Parameters.Add(new SqlParameter("@UserId", user.ProviderUserKey));
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
        }


        public override void AddToRole<P>(string roleName, P permFlag)
        {
            var SecFlag = GetSecurityFlag(permFlag);
            using (var SqlConn = new SqlConnection(this._ConnString))
            {
                var sql = @"INSERT INTO dbo.PermissionsInRoles (RoleId,ModuleId, MenuId, FuncId, FlagName) 
                            SELECT r.RoleId, @ModuleId, @MenuId, @FuncId, @FlagName FROM dbo.aspnet_Roles r WHERE (LoweredRoleName= @RoleName ) 
                            AND (NOT EXISTS (SELECT * FROM PermissionsInRoles p WHERE (p.RoleId = r.RoleId) AND (ModuleId = @ModuleId) AND (p.MenuId = @MenuId) AND (p.FuncId = @FuncId)))";

                var command = new SqlCommand(sql, SqlConn);
                command.Parameters.Add(new SqlParameter("@RoleName", roleName.ToLower()));
                command.Parameters.Add(new SqlParameter("@ModuleId", SecFlag.Module));
                command.Parameters.Add(new SqlParameter("@MenuId", SecFlag.Flag));
                command.Parameters.Add(new SqlParameter("@FuncId", Convert.ToInt32(permFlag)));
                command.Parameters.Add(new SqlParameter("@FlagName", getFlagName(permFlag)));
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
        }


        //public override void RemoveFromUser<P>(string userName, P permFlag)
        public override void RemoveFromUser<P>(MembershipUser user, P permFlag)
        {
            var SecFlag = GetSecurityFlag(permFlag);
            using (var SqlConn = new SqlConnection(this._ConnString))
            {
                var sql = @"DELETE dbo.UsersInPermissions WHERE (UserId = @UserId) AND (UserName = @UserName) AND (ModuleId = @ModuleId) AND (MenuId = @MenuId) AND (FuncId = @FuncId)";

                var command = new SqlCommand(sql, SqlConn);
                command.Parameters.Add(new SqlParameter("@UserName", user.UserName.ToLower()));
                command.Parameters.Add(new SqlParameter("@UserId", user.ProviderUserKey));
                command.Parameters.Add(new SqlParameter("@ModuleId", SecFlag.Module));
                command.Parameters.Add(new SqlParameter("@MenuId", SecFlag.Flag));
                command.Parameters.Add(new SqlParameter("@FuncId", Convert.ToInt32(permFlag)));
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public override void RemoveFromRole<P>(string roleName, P permFlag)
        {
            var SecFlag = GetSecurityFlag(permFlag);
            using (var SqlConn = new SqlConnection(this._ConnString))
            {
                var sql = @"DELETE [dbo].[PermissionsInRoles] FROM [dbo].[PermissionsInRoles] p, [dbo].[aspnet_Roles] r
                            WHERE (p.RoleId = r.RoleId) AND (r.RoleName = @RoleName) AND (ModuleId = @ModuleId) AND (p.MenuId = @MenuId) AND (p.FuncId = @FuncId)";

                var command = new SqlCommand(sql, SqlConn);
                command.Parameters.Add(new SqlParameter("@RoleName", roleName));
                command.Parameters.Add(new SqlParameter("@ModuleId", SecFlag.Module));
                command.Parameters.Add(new SqlParameter("@MenuId", SecFlag.Flag));
                command.Parameters.Add(new SqlParameter("@FuncId", Convert.ToInt32(permFlag)));
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
        }


        public override void RemoveAllFromUser(MembershipUser user)
        {
            using (var SqlConn = new SqlConnection(this._ConnString))
            {
                var sql = @"DELETE [dbo].[UsersInPermissions] WHERE (UserId = @UserId) AND (UserName = @UserName)";

                var command = new SqlCommand(sql, SqlConn);
                command.Parameters.Add(new SqlParameter("@UserId", user.ProviderUserKey));
                command.Parameters.Add(new SqlParameter("@UserName", user.UserName.ToLower()));
                command.Connection.Open();
                command.ExecuteNonQuery();
            }   
        }


        #region Private

        string getFlagName<P>(P permFlag)
        {
            var SecFlag = GetSecurityFlag(permFlag);
            return string.Format("{0}.{1}", permFlag.GetType().Name, permFlag.ToString());
        }

        #endregion



    
    }
}
